1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmServicesRecord
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID order by ServiceCreationDate", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 fillServiceCode()
25 End Sub
26
27 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
28 Try
29 If dgw.Rows.Count > 0 Then
30 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
31 If lblSet.Text = "Services" Then
32 frmServices.Show()
33 Me.Hide()
34 frmServices.txtID.Text = dr.Cells(0).Value.ToString()
35 frmServices.txtServiceCode.Text = dr.Cells(1).Value.ToString()
36 frmServices.dtpServiceCreationDate.Text = dr.Cells(2).Value.ToString()
37 frmServices.txtCustomerID.Text = dr.Cells(4).Value.ToString()
38 frmServices.txtCID.Text = dr.Cells(3).Value.ToString()
39 frmServices.txtCustomerName.Text = dr.Cells(5).Value.ToString()
40 frmServices.cmbServiceType.Text = dr.Cells(6).Value.ToString()
41 frmServices.txtItemsDescription.Text = dr.Cells(7).Value.ToString()
42 frmServices.txtProblemDescription.Text = dr.Cells(8).Value.ToString()
43 frmServices.txtChargesQuote.Text = dr.Cells(9).Value.ToString()
44 frmServices.txtUpfront.Text = dr.Cells(10).Value.ToString()
45 frmServices.dtpEstimatedRepairDate.Text = dr.Cells(11).Value.ToString()
46 frmServices.cmbStatus.Text = dr.Cells(12).Value.ToString()
47 frmServices.txtRemarks.Text = dr.Cells(13).Value.ToString()
48 frmServices.btnSave.Enabled = False
49 frmServices.btnUpdate.Enabled = True
50 frmServices.btnPrint.Enabled = True
51 frmServices.btnDelete.Enabled = True
52 End If
53 End If
54 Catch ex As Exception
55 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
56 End Try
57 End Sub
58
59 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
60 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
61 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
62 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
63 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
64 End If
65 Dim b As Brush = SystemBrushes.ControlText
66 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
67
68 End Sub
69 Sub fillServiceCode()
70 Try
71 con = New SqlConnection(cs)
72 con.Open()
73 adp = New SqlDataAdapter()
74 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ServiceCode) FROM Service", con)
75 ds = New DataSet("ds")
76 adp.Fill(ds)
77 dtable = ds.Tables(0)
78 cmbServiceCode.Items.Clear()
79 For Each drow As DataRow In dtable.Rows
80 cmbServiceCode.Items.Add(drow(0).ToString())
81 Next
82 Catch ex As Exception
83 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
84 End Try
85 End Sub
86 Sub Reset()
87 cmbServiceCode.Text = ""
88 txtCustomerName.Text = ""
89 fillServiceCode()
90 dtpDateFrom.Text = Today
91 dtpDateTo.Text = Today
92 DateTimePicker2.Text = Today
93 DateTimePicker1.Text = Today
94 cmbStatus.SelectedIndex = -1
95 Getdata()
96 End Sub
97 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
98 Reset()
99 End Sub
100
101 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
102 Me.Close()
103 End Sub
104
105
106 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
107 Dim rowsTotal, colsTotal As Short
108 Dim I, j, iC As Short
109 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
110 Dim xlApp As New Excel.Application
111 Try
112 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
113 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
114 xlApp.Visible = True
115
116 rowsTotal = dgw.RowCount
117 colsTotal = dgw.Columns.Count - 1
118 With excelWorksheet
119 .Cells.Select()
120 .Cells.Delete()
121 For iC = 0 To colsTotal
122 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
123 Next
124 For I = 0 To rowsTotal - 1
125 For j = 0 To colsTotal
126 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
127 Next j
128 Next I
129 .Rows("1:1").Font.FontStyle = "Bold"
130 .Rows("1:1").Font.Size = 12
131
132 .Cells.Columns.AutoFit()
133 .Cells.Select()
134 .Cells.EntireColumn.AutoFit()
135 .Cells(1, 1).Select()
136 End With
137 Catch ex As Exception
138 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
139 Finally
140 'RELEASE ALLOACTED RESOURCES
141 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
142 xlApp = Nothing
143 End Try
144 End Sub
145
146 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
147 Try
148 con = New SqlConnection(cs)
149 con.Open()
150 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and ServiceCreationDate between @d1 and @d2 order by ServiceCreationDate", con)
151 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
152 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
153 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
154 dgw.Rows.Clear()
155 While (rdr.Read() = True)
156 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
157 End While
158 con.Close()
159 Catch ex As Exception
160 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
161 End Try
162 End Sub
163
164 Private Sub cmbOrderNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbServiceCode.SelectedIndexChanged
165 Try
166 con = New SqlConnection(cs)
167 con.Open()
168 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and ServiceCode='" & cmbServiceCode.Text & "' order by ServiceCreationDate", con)
169 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
170 dgw.Rows.Clear()
171 While (rdr.Read() = True)
172 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
173 End While
174 con.Close()
175 Catch ex As Exception
176 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
177 End Try
178 End Sub
179
180 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
181 Try
182 If cmbStatus.Text = "" Then
183 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
184 cmbStatus.Focus()
185 Exit Sub
186 End If
187 con = New SqlConnection(cs)
188 con.Open()
189 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and ServiceCreationDate between @d1 and @d2 and Status='" & cmbStatus.Text & "' order by ServiceCreationDate", con)
190 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
191 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date
192 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
193 dgw.Rows.Clear()
194 While (rdr.Read() = True)
195 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9))
196 End While
197 con.Close()
198 Catch ex As Exception
199 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
200 End Try
201 End Sub
202
203 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
204 Try
205 con = New SqlConnection(cs)
206 con.Open()
207 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and Name like '%" & txtCustomerName.Text & "%' order by ServiceCreationDate", con)
208 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
209 dgw.Rows.Clear()
210 While (rdr.Read() = True)
211 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
212 End While
213 con.Close()
214 Catch ex As Exception
215 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
216 End Try
217 End Sub
218
219 Private Sub cmbInvoiceNo_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbServiceCode.Format
220 If (e.DesiredType Is GetType(String)) Then
221 e.Value = e.Value.ToString.Trim
222 End If
223 End Sub
224 End Class
